A couple of years ago, I applied for an undergraduate program at Barry University. This morning I met a colleague reading a book about preparing for success in healthcare information and management systems. Honestly, I have no idea about previous editions, but the third edition’s cover image gathers people of all races. Suddenly, I was a little bit curious to browse quickly the preface and author biographies. When I fell on one of the authors, Melissa Barthold. I discovered that she was an adjunct professor at Barry University in the School of Nursing. This reminded me of the application submitted to this University.
I started to search for all emails I sent that contain the word “Barry” and I got five results.
After clicking on the first one, I realized that the content was absolutely different from my expectation. Contrariwise, the keyword matched with the sender name. This email was my reply to the selection test that a company sent me. I was looking for A but I found B.
Find below my response to the question I was asked:
Write a PL/SQL block which will populate the RESULTS table as described below. Consider performance implications (specifically where would you commit the queries) as we are dealing with millions of records. This query is suppose to cater for large volumes of data.
The PL/SQL must:
Listed below are the tables & definitions:
DECLARE
VARCHAR2 (100) := 'Recruitment and Selection Test of Jean Marie Cimula';
BEGIN
DBMS_OUTPUT.put_line (ENTERPRISE);
---insert into the RESULTS table each customer id,
---and the number of unique products purchased by that customer.
INSERT INTO RESULTS (customer_id,product_count)
SELECT customer_id,COUNT(product_id) product_count
FROM (
SELECT distinct customer_id,product_id
FROM CUST_PRODUCTS
)
GROUP BY customer_id;
COMMIT;
---update the recently_purchased column of the customer table
MERGE INTO CUSTOMER B
USING(
SELECT customer_id,
CASE WHEN
ROUND(MONTHS_BETWEEN (SYSDATE,TO_DATE(date_purchased,'MM-DD-YYYY'))) <= 12 THEN 'Y'
ELSE 'N' END date_purchased
FROM CUST_PRODUCTS
) T ON( B.customer_id= T.customer_id)
WHEN MATCHED THEN UPDATE SET B.recently_purchased=T.date_purchased;
COMMIT;
END;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
(DBMS_UTILITY.format_error_stack);
END;